package com.library.dao.impl;

import com.library.dao.StaffDao;
import com.library.model.Staff;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

@Repository
public class StaffDaoImpl implements StaffDao {

    private final JdbcTemplate jdbcTemplate;

    public StaffDaoImpl(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public List<Staff> findAll() {
        String sql = "SELECT * FROM staff";
        return jdbcTemplate.query(sql, new StaffRowMapper());
    }

    @Override
    public List<Staff> findByCriteria(Map<String, Object> criteria) {
        StringBuilder sql = new StringBuilder("SELECT * FROM staff WHERE 1=1");
        Object[] params = new Object[criteria.size()];
        int index = 0;

        for (Map.Entry<String, Object> entry : criteria.entrySet()) {
            String key = entry.getKey();
            Object value = entry.getValue();

            if (value != null) {
                sql.append(" AND ").append(key).append(" = ?");
                params[index++] = value;
            }
        }

        return jdbcTemplate.query(sql.toString(), new StaffRowMapper(), params);
    }

    @Override
    public Staff findById(Long id) {
        String sql = "SELECT * FROM staff WHERE id = ?";
        try {
            return jdbcTemplate.queryForObject(sql, new StaffRowMapper(), id);
        } catch (Exception e) {
            return null;
        }
    }

    @Override
    public Staff findByEmployeeId(String employeeId) {
        String sql = "SELECT * FROM staff WHERE employee_id = ?";
        try {
            return jdbcTemplate.queryForObject(sql, new StaffRowMapper(), employeeId);
        } catch (Exception e) {
            return null;
        }
    }

    @Override
    public int save(Staff staff) {
        String sql = "INSERT INTO staff (name, employee_id, position, email, phone, hire_date) " +
                "VALUES (?, ?, ?, ?, ?, ?)";
        return jdbcTemplate.update(sql,
                staff.getName(),
                staff.getEmployeeId(),
                staff.getPosition(),
                staff.getEmail(),
                staff.getPhone(),
                // 将java.util.Date转换为java.sql.Date
                staff.getHireDate() != null ? new Date(staff.getHireDate().getTime()) : null);
    }

    @Override
    public int update(Staff staff) {
        String sql = "UPDATE staff SET name = ?, employee_id = ?, position = ?, email = ?, " +
                "phone = ?, hire_date = ? WHERE id = ?";
        return jdbcTemplate.update(sql,
                staff.getName(),
                staff.getEmployeeId(),
                staff.getPosition(),
                staff.getEmail(),
                staff.getPhone(),

                staff.getHireDate() != null ? new Date(staff.getHireDate().getTime()) : null,
                staff.getId());
    }

    @Override
    public int delete(Long id) {
        String sql = "DELETE FROM staff WHERE id = ?";
        return jdbcTemplate.update(sql, id);
    }

    private static class StaffRowMapper implements RowMapper<Staff> {
        @Override
        public Staff mapRow(ResultSet rs, int rowNum) throws SQLException {
            Staff staff = new Staff();
            staff.setId(rs.getLong("id"));
            staff.setName(rs.getString("name"));
            staff.setEmployeeId(rs.getString("employee_id"));
            staff.setPosition(rs.getString("position"));
            staff.setEmail(rs.getString("email"));
            staff.setPhone(rs.getString("phone"));

            java.sql.Date sqlHireDate = rs.getDate("hire_date");
            if (sqlHireDate != null) {
                staff.setHireDate(new java.util.Date(sqlHireDate.getTime()));
            }

            java.sql.Timestamp createdAt = rs.getTimestamp("created_at");
            if (createdAt != null) {
                staff.setCreatedAt(new java.util.Date(createdAt.getTime()));
            }

            java.sql.Timestamp updatedAt = rs.getTimestamp("updated_at");
            if (updatedAt != null) {
                staff.setUpdatedAt(new java.util.Date(updatedAt.getTime()));
            }

            return staff;
        }
    }
}
